﻿using System.Data;
using NPOI.HSSF.UserModel;
using Npoi.Mapper;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

namespace Common.Core
{
    public class ExcelHelper
    {
        /// <summary>
        /// 将多个DataTable数据导入到excel的多个sheet（名称取DataTable的tableName）中
        /// </summary>
        /// <param name="dts">要导入的数据集合</param>
        public static byte[]? GridToExcel(List<DataTable> dts)
        {
            try
            {
                HSSFWorkbook workbook = new HSSFWorkbook();
                foreach (DataTable dt in dts)
                {
                    SetHssfWorkbook(dt, workbook);
                }
                using MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                return stream.ToArray();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }


        /// <summary>
        /// 将多个DataTable数据导入到多个excel中
        /// </summary>
        /// <param name="dts">要导入的数据集合</param>
        public static IEnumerable<byte[]> GridToExcelList(List<DataTable> dts)
        {
            List<byte[]> result =new List<byte[]>();
            foreach (DataTable dt in dts)
            {
                try
                {
                    HSSFWorkbook workbook = new HSSFWorkbook();
                    SetHssfWorkbook(dt, workbook);
                    using MemoryStream stream = new MemoryStream();
                    workbook.Write(stream);
                    var streamArray = stream.ToArray();
                    result.Add(streamArray);
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                }
            }

            return result;

        }

        private static HSSFWorkbook SetHssfWorkbook(DataTable dt, HSSFWorkbook workbook)
        {
            ISheet sheet = workbook.CreateSheet(dt.TableName);
            ICellStyle headercellStyle = workbook.CreateCellStyle();
            headercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            headercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            //字体
            NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
            headerfont.IsBold = true;
            headercellStyle.SetFont(headerfont);


            //用column name 作为列名
            int icolIndex = 0;
            IRow headerRow = sheet.CreateRow(0);
            foreach (DataColumn item in dt.Columns)
            {
                ICell cell = headerRow.CreateCell(icolIndex);
                cell.SetCellValue(item.ColumnName);
                cell.CellStyle = headercellStyle;
                icolIndex++;
            }


            ICellStyle cellStyle = workbook.CreateCellStyle();
            //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text来看
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
            cellfont.IsBold = false;
            cellStyle.SetFont(cellfont);


            //建立内容行
            int iRowIndex = 1;
            int iCellIndex = 0;
            foreach (DataRow rowitem in dt.Rows)
            {
                IRow DataRow = sheet.CreateRow(iRowIndex);
                foreach (DataColumn colitem in dt.Columns)
                {
                    ICell cell = DataRow.CreateCell(iCellIndex);
                    cell.SetCellValue(rowitem[colitem].ToString());
                    cell.CellStyle = cellStyle;
                    iCellIndex++;
                }

                iCellIndex = 0;
                iRowIndex++;
            }

            //自适应列宽
            for (int i = 0; i < icolIndex; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            return workbook;
        }

        /// <summary>
        /// List转Excel
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list">数据</param>
        /// <param name="sheetName">表名</param>
        /// <param name="overwrite">true,覆盖单元格，false追加内容(list和创建的excel或excel模板)</param>
        /// <param name="xlsx">true-xlsx，false-xls</param>
        /// <returns>返回文件</returns>
        public static MemoryStream ParseListToExcel<T>(List<T> list, string sheetName = "sheet1", bool overwrite = true, bool xlsx = true) where T : class
        {
            var mapper = new Mapper();
            MemoryStream ms = new MemoryStream();
            mapper.Save<T>(ms, list, sheetName, overwrite, xlsx);
            return ms;
        }
        /// <summary>
        /// Excel转为List
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="fileStream"></param>
        /// <param name="sheetname"></param>
        /// <returns></returns>
        public static List<T> ParseExcelToList<T>(Stream fileStream, string sheetname = "") where T : class
        {
            List<T> ModelList = new List<T>();
            var mapper = new Mapper(fileStream);
            List<RowInfo<T>> DataList = new List<RowInfo<T>>();
            if (!string.IsNullOrEmpty(sheetname))
            {
                DataList = mapper.Take<T>(sheetname).ToList();
            }
            else
            {
                DataList = mapper.Take<T>().ToList();
            }

            if (DataList != null && DataList.Count > 0)
            {
                foreach (var item in DataList)
                {
                    ModelList.Add(item.Value);
                }
            }
            return ModelList;
        }
    }
}
